[MySQL] Optimize Query
Posted
by
bordeux
on Stack Overflow
See other posts from Stack Overflow
or by bordeux
Published on 2011-01-15T17:13:05Z
Indexed on
2011/01/15
17:53 UTC
Read the original article
Hit count: 144
Hello. I have problem with optimize this query:
SET @SEARCH = "dokumentalne"; SELECT SQL_NO_CACHE `AA`.`version` AS `Version` , `AA`.`contents` AS `Contents` , `AA`.`idarticle` AS `AdressInSQL` , `AA` .`topic` AS `Topic` , MATCH (`AA`.`topic` , `AA`.`contents`) AGAINST (@SEARCH) AS `Relevance` , `IA`.`url` AS `URL` FROM `xv_article` AS `AA` INNER JOIN `xv_articleindex` AS `IA` ON ( `AA`.`idarticle` = `IA`.`adressinsql` ) INNER JOIN ( SELECT `idarticle` , MAX( `version` ) AS `version` FROM `xv_article` WHERE MATCH (`topic` , `contents`) AGAINST (@SEARCH) GROUP BY `idarticle` ) AS `MG` ON ( `AA`.`idarticle` = `MG`.`idarticle` ) WHERE `IA`.`accepted` = "yes" AND `AA`.`version` = `MG`.`version` ORDER BY `Relevance` DESC LIMIT 0 , 30
Now, this query using ^ 20 seconds. How to optimize this?
EXPLAIN gives this:
1 PRIMARY AA ALL NULL NULL NULL NULL 11169 Using temporary; Using filesort 1 PRIMARY ALL NULL NULL NULL NULL 681 Using where 1 PRIMARY IA ALL accepted NULL NULL NULL 11967 Using where 2 DERIVED xv_article fulltext topic topic 0 1 Using where; Using temporary; Using filesort
This is example server with my data:
user: bordeux_4prog password: 4prog phpmyadmin: http://phpmyadmin.bordeux.net/ chive: http://chive.bordeux.net/
© Stack Overflow or respective owner